<?php
/**
 * 成績檔升級
 * @author cik
 *
 */
class Default_Model_Upgrade_ScoreMaster extends Default_Model_Upgrade_Core
{
    protected $_table="score_master";

    protected $_targetTable = array('student_score_stage');

    public function upgrade()
    {
         $table_name = "student_score_stage_detail";
        if ($this->isUpgrade())
        return 'isUpgrde';
        else {


            // 先改為 MYISM
            $query = "ALTER TABLE {$table_name}  DROP FOREIGN KEY {$table_name}_ibfk_1";
            $this->_newdbPdo->exec($query);

            $query = "ALTER TABLE `{$table_name}`  ENGINE = MYISAM";
            $this->_newdbPdo->exec($query);


            $query = "SELECT year,semester FROM score_ss GROUP BY year,semester";
            $res = $this->_sfs3Pdo->query($query)->fetchAll(PDO::FETCH_ASSOC);
            foreach ($res as $row) {
                // 段考成績
                // TODO detail 資料表有誤
                $table_name_1 = "score_semester_".$row['year']."_".$row['semester'];
                $query = "SHOW TABLES LIKE '$table_name_1'";
                $count = $this->_sfs3Pdo->query($query);
                if ($count->rowCount()>0) {
                    $query = "SELECT  ss_id,class_id,test_kind,test_sort,sendmit,teacher_sn,update_time  FROM
                    $table_name_1  WHERE ss_id>0 GROUP BY  ss_id,class_id,test_kind,test_sort ";
                    $res2 = $this->_sfs3Pdo->query($query)->fetchall(PDO::FETCH_ASSOC);
                    $temp_arr = array();

                    foreach($res2 as $row2) {
                        $arr = array();
                        $arr['subject_sn'] = $row2['ss_id'];
                        $test_kind = $this->iconv($row2['test_kind']);
                        if ($test_kind == '定期評量')
                        $arr['test_kind'] = 1;
                        elseif ($test_kind=='平時成績')
                        $arr['test_kind'] = 2;
                        elseif ($test_kind=='全學期')
                        $arr['test_kind'] =3;
                        // 班級
                        $classTemp = explode("_",$row2['class_id']);
                        $arr['class_sn'] = (int)end($classTemp);

                        $arr['test_sort'] = $row2['test_sort'];

                        $arr['sendmit']  = ($row2['sendmit']=='1')?'N':'Y';
                        $arr['teacher_sn'] = $row2['teacher_sn'];
                        $arr['update_time'] = $row2['update_time'];
                        $sn = $this->fromArray('student_score_stage',$arr);
                        $temp_arr[$row2['class_id']][$row2['ss_id']][$arr['test_kind']][$row2['test_sort']] =  $sn ; //$this->newdb->lastInsertId('student_score_stage');
                    }



                    $query = "SELECT * FROM $table_name_1 WHERE ss_id>0 and score <= 100 and score>=0 ";
                    $score_res = $this->_sfs3Pdo->query($query)->fetchAll(PDO::FETCH_ASSOC);
                    foreach($score_res as $score_row) {
//                        $query = "SELECT sn FROM student_score_stage WHERE
//                            subject_sn={$score_row['ss_id']} AND test_kind={$sc}
//                        "
                        $arr2= array();
                        $test_kind = $this->iconv($score_row['test_kind']);
                        if ($test_kind == '定期評量')
                        $test_kind = 1;
                        elseif ($test_kind=='平時成績')
                        $test_kind = 2;
                        elseif ($test_kind=='全學期')
                        $test_kind =3;
                        $arr2['student_sn'] = $score_row['student_sn'];
                        if ($score_row['score'] >=0)
                        $arr2['score'] = $score_row['score'];
                        $arr2['teacher_sn'] = $score_row['teacher_sn'];
//                        $classTemp = explode("_",$score_row['class_id']);
//                        $class_sn = (int)end($classTemp);

                        $arr2['stage_sn'] = $temp_arr[$score_row['class_id']][$score_row['ss_id']][$test_kind][$score_row['test_sort']];
                        if ($arr2['stage_sn'])
                        $this->fromArray($table_name,$arr2);

                    }
                    //                    $query = "ALTER TABLE `{$table_name}`  ENGINE = INNODB";
                    //                     $this->_newdbPdo->exec($query);
                    //
                    //                     $query = "ALTER TABLE `$table_name`
                    //  ADD CONSTRAINT `{$table_name}_ibfk_1` FOREIGN KEY (`stage_sn`) REFERENCES `student_score_stage` (`sn`) ON DELETE CASCADE ON UPDATE CASCADE;";
                    //                    $this->_newdbPdo->exec($query);

                }

            }
            $query = "ALTER TABLE `{$table_name}`  ENGINE = INNODB";
            $this->_newdbPdo->exec($query);

            $query = "ALTER TABLE `$table_name`
  ADD CONSTRAINT `{$table_name}_ibfk_1` FOREIGN KEY (`stage_sn`) REFERENCES `student_score_stage` (`sn`) ON DELETE CASCADE ON UPDATE CASCADE;";
            $this->_newdbPdo->exec($query);

            $log['memo'] = '階段成績';
            $log['upgrade_time']	= date("Y-m-d h:i:s");
            $this->finishUpgrade($log);
        }
    }
}
